package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.Set;
import java.util.Vector;

import main.Main;
import objects.Teacher;

public interface IDBTeacher extends IDBContact {
	
	public class DBTeacher extends DBContact {
		
		/*
		 * methods are final for optimization of the code (by the compiler)
		 * it is not possible anymore to inherit it after this declaration 
		 */
		
		public final static Set<String> titles = new LinkedHashSet<String>();
		static {
			titles.add("");
			titles.add("Prof.");
			titles.add("Dr.");
			titles.add("Ir.");
			titles.add("Prof. Dr.");
			titles.add("Prof. Ir.");
			titles.add("Dr. Ir.");
			titles.add("Prof. Dr. Ir.");
		}
		
		
		public final void init() throws Exception {
			
			createTable();
			createTriggers();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table Teacher( " +
						   "Email varchar(" + maxLengthEmail + ") unique not null, " +
						   "Office varchar(" + maxLengthOffice + ") not null, " +
						   "Title varchar(" + maxLengthTitle + "), " +
						   "foreign key (Email) references Contact (Email), " +
						   "primary key (Email) " +
						       "on conflict abort, " +
						   "check (Title in ('','Prof.','Dr.','Ir.'," +
						       "'Prof. Dr.','Prof. Ir.','Dr. Ir.','Prof. Dr. Ir.')));";
			
			executeCreation(query);
		}
		
		private final void createTriggers() throws Exception {
			
			String query;
			query = "create trigger update_teacher_email after update of Email on Teacher " +
			   	    "begin " +
				    	"update RelCourseTeacher set Teacher_Email = new.Email " +
				    		"where (Teacher_Email = old.Email); " +
				    "end;";
			executeCreation(query);
			
			query = "create trigger delete_teacher_email before delete on Teacher " +
					"begin " +
	    	   			"delete from RelCourseTeacher " +
	    	   				"where (Teacher_Email = old.Email); " +
	    	   		"end;";
			executeCreation(query);
		}
		
		public final boolean add(String email, String name, String fname, String phoneNr, 
				String moreInfo, String office, String title) throws Exception {
			
			checkConsistance(email, name, fname, phoneNr, moreInfo, office, title);
			
			boolean success = super.add(email, name, fname, phoneNr, moreInfo);
			
			Vector<Object> v = new Vector<Object>(3);
			v.add(0, email);
			v.add(1, office);
			v.add(2, title);
			
			return (success && super.add("Teacher", v));
		}
		
		public final boolean updateOffice(String email, String newOffice) throws Exception {
			
			checkEmail(email);
			checkOffice(newOffice);
			
			return super.update("Teacher", "Email", email, "Office", newOffice);
		}
		
		public final boolean updateTitle(String email, String newTitle) throws Exception {
			
			checkEmail(email);
			checkTitle(newTitle);
			
			return super.update("Teacher", "Email", email, "Title", newTitle);
		}
		
		public Teacher getTeacher(String email) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Teacher natural join Contact " +
											 "where (Teacher.Email = '" + email + "');");
			if (rs.next()) {
				Teacher t = new Teacher(rs.getString("Email"), rs.getString("Name"),
						rs.getString("Forename"), rs.getString("PhoneNumber"), 
						rs.getString("MoreInformation"), rs.getString("Office"), 
						rs.getString("Title"));
				rs.close();
				return t;
			}
			rs.close();
			return null;
		}
		
		
		public Vector<Teacher> getAllTeachers() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Teacher natural join Contact;");
			
			Vector<Teacher> v = new Vector<Teacher>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new Teacher(rs.getString("Email"), rs.getString("Name"),
					rs.getString("Forename"), rs.getString("PhoneNumber"), 
					rs.getString("MoreInformation"), rs.getString("Office"), 
					rs.getString("Title")));
				counter++;
			}
			rs.close();
			return v;
		}
		
		private final void checkConsistance(String email, String name, String fname, String phoneNr, 
				String moreInfo, String office, String title) throws Exception {
			
			super.checkConsistance(email, name, fname, phoneNr, moreInfo);
			checkOffice(office);
			checkTitle(title);
		}
		
		private final void checkOffice(String office) throws Exception {
			
			if (office == null) { throw (new Exception("Office needed!")); }
			if (office.length() < 1) {
				throw (new Exception("Office has to be at least 1 character long!"));
			}
			if (office.length() > maxLengthOffice) {
				throw (new Exception("Office exceeds maximum length!"));
			}
		}
		
		private final void checkTitle(String title) throws Exception {
			
			if (title == null) { throw (new Exception("Title needed!")); }
			if (title.length() < 1) { title = ""; }
			if (title.length() > maxLengthTitle) {
				throw (new Exception("Title exceeds maximum length!"));
			}
			if (!titles.contains(title)) {
				String message = "Title has to be one of the following: (Nothing)";
				Iterator<String> it = titles.iterator();
				it.next(); // skipping the empty string
				while (it.hasNext()) {
					message += ", " + it.next();
				}
				throw (new Exception(message));
			}
		}
		
		
		private final int maxLengthOffice = 5;
		private final int maxLengthTitle = 13;
		
	}
	
}
